package model.student;
import java.io.File;
import java.sql.ResultSet;
import java.util.*;
import java.util.Calendar;
import javax.print.attribute.standard.DateTimeAtCompleted;
import javax.servlet.http.HttpSession;
import myutil.Connect;
import org.apache.commons.io.FileUtils;
import org.apache.struts2.ServletActionContext;
import java.text.*;
import sun.misc.Compare;
import vo.DocumentListVo;
import vo.Placementvo;

public class UploadCv{
	ResultSet rs;
	int latest_document_id;
	String query;
	int student_id,batch_id;
	int count=0;
	ArrayList<DocumentListVo> cvlist=new ArrayList<DocumentListVo>();
	ArrayList<Placementvo> companylist= new ArrayList<Placementvo>();
	public void uploadmycv(File attached_file, String file_name){
		try 
		{
			HttpSession session = ServletActionContext.getRequest().getSession();
			String uploader_email_id = session.getAttribute("username").toString();
			if (attached_file != null) 
			{
				Connect c = new Connect();
				// int uploader_id=0;
				
				
				query="SELECT sd.student_id, batch_id FROM student_details sd, student_batch sb WHERE sd.email_id ='"+uploader_email_id+"' AND sd.student_id = sb.student_id";
				System.out.println(query);
				rs=c.getResult(query);
				while(rs.next()){
					student_id=rs.getInt("student_id");
					batch_id=rs.getInt("batch_id");
				}
				rs=c.getResult("select * from student_resume where student_id="+student_id+" and batch_id="+batch_id);
				while(rs.next()){
					count=count+1;
				}
				if(count==0){
				c.dml("insert into document_upload(uploader_email_id,document_name) values('"
						+ uploader_email_id + "','" + file_name + "')");

				rs = c.getResult("select max(document_id) as max_id from document_upload");
				while (rs.next()) 
				{
					latest_document_id = rs.getInt("max_id");
					System.out.println(latest_document_id);
				}
				
				
				query="insert into student_resume values('',"+student_id+","+latest_document_id+","+batch_id+")";
				System.out.println(query);
				c.dml(query);
				// System.out.println(getServlet().getServletContext().getRealPath(""));
				File f = new File(
						"F:/Metalink/attachments/"
								+ batch_id);
				File f1=new File(
						"F:/Metalink/attachments/"+ batch_id+"/"+uploader_email_id);
				
				if(f.exists()){
					
					f1.mkdir();
				}
				else{
					f.mkdir();
					f1.mkdir();
				}
			
				File f2 = new File(
						"F:/Metalink/attachments/"+ batch_id + "/" + uploader_email_id + "/" +file_name);
				FileUtils.copyFile(attached_file, f2);
			}
				else {
					int document_id=0;String prev_file_name="";
					query="select document_id from student_resume where student_id="+student_id+" and batch_id="+batch_id;
					System.out.println(query);
					rs= c.getResult(query);
					while(rs.next()){
						document_id=rs.getInt("document_id");
					}
					rs=c.getResult("select document_name from document_upload where document_id="+document_id);
					while(rs.next()){
						prev_file_name=rs.getString("document_name");
					}
					File f1=new File(
							"F:/Metalink/attachments/"+ batch_id+"/"+uploader_email_id+"/"+prev_file_name);
					boolean s=f1.delete();
					System.out.println("deleted file is:"+s);
					c.dml("update document_upload set document_name='"+file_name+"',upload_date=sysdate() where document_id="+document_id);
					File f2 = new File(
							"F:/Metalink/attachments/"+ batch_id + "/" + uploader_email_id + "/" +file_name);
					FileUtils.copyFile(attached_file, f2);
				}
				}

		}
		catch (Exception e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
	}
	
	public ArrayList<DocumentListVo> getResumeList(){
		ArrayList<DocumentListVo> doclist = new ArrayList<DocumentListVo>();
		HttpSession session = ServletActionContext.getRequest()
				.getSession();
		String uploader_email_id = session.getAttribute("username")
				.toString();
		
		Connect c=new Connect();
		query="SELECT sd.student_id, batch_id FROM student_details sd, student_batch sb WHERE sd.email_id ='"+uploader_email_id+"' AND sd.student_id = sb.student_id";
		
		try {
			rs=c.getResult(query);
			while(rs.next()){
				student_id=rs.getInt("student_id");
				batch_id=rs.getInt("batch_id");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("id is in :"+student_id);
		query="select du.document_id,document_name,upload_date from document_upload du,student_resume sr where sr.student_id="+student_id+" and batch_id="+batch_id+" and sr.document_id=du.document_id";
		System.out.println(query);
		try {
			rs=c.getResult(query);
			while(rs.next()){
				DocumentListVo vo = new DocumentListVo();
				vo.setAttachment_id(rs.getInt("document_id"));
				vo.setDocument_name(rs.getString("document_name"));
				vo.setUpload_d(rs.getString("upload_date"));
				vo.setBatch_id(batch_id);
				vo.setPath(batch_id+"/"+uploader_email_id);
				cvlist.add(vo);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return cvlist;
	}
	
	public void deletecv(int document_id){
		Connect c= new Connect();
		String query;
		query="update document_upload set document_name='' and upload_date='' where document_id="+document_id;
		System.out.println(query);
		try {
			c.dml(query);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public ArrayList<Placementvo> getCompanyList(){
		HttpSession session = ServletActionContext.getRequest()
				.getSession();
		String uploader_email_id = session.getAttribute("username")
				.toString();
		 Date currentDate = new Date();
		 String value="",company_name="";
		 SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		Connect c=new Connect();
		System.out.println(currentDate);
		Date convertedDate;
		query="SELECT sd.student_id, batch_id FROM student_details sd, student_batch sb WHERE sd.email_id ='"+uploader_email_id+"' AND sd.student_id = sb.student_id";
		
		try {
			rs=c.getResult(query);
			while(rs.next()){
				student_id=rs.getInt("student_id");
				batch_id=rs.getInt("batch_id");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("id is in :"+student_id);
		query="select cd.company_name,visit_date,package_offered from company_visit cv,company_details cd where batch_id="+batch_id+" and cv.company_id=cd.company_id";
		System.out.println(query);
		try {
			rs=c.getResult(query);
			while(rs.next()){
				convertedDate=null;
				
				String company_date="";
				Placementvo vo = new Placementvo();
				
				company_name=rs.getString("company_name");
				company_date=(rs.getString("visit_date"));
				 convertedDate = dateFormat.parse(company_date);
				 System.out.println(convertedDate);
				 boolean datecompare;
				 datecompare=convertedDate.after(currentDate);
				 if (datecompare==false){
					 company_name=company_name.concat("--VISITED");
				 }
				 vo.setVisit_date(company_date);
				 vo.setCompany_name(company_name);
				 System.out.println(company_name);
				 System.out.println(datecompare);
				vo.setPackage_offered(rs.getString("package_offered"));
				companylist.add(vo);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return companylist;
		
	}

	public ArrayList<Placementvo> getPlacementCal(){
		ArrayList<Placementvo> cal = new ArrayList<Placementvo>();
		String query="";
		Connect c= new Connect();
		query="";
		
		return cal;
	}
}
